/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.ntb.models.impl;

import com.ntb.entities.Contact;
import com.ntb.common.DatabaseConnection;
import com.ntb.models.ContactDAO;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Thinh Hung
 */
public class ContactDAOImpl extends DatabaseConnection implements ContactDAO {

    public int countAll() {
        try {
            openConnection();
            ResultSet rs = getResultset("Select Count(ContactID) from Contacts");
            int result = 0;
            if (rs.next()) {
                result = rs.getInt(1);
            }
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return 0;
    }

    public Iterator<Contact> getLast(int count) {
        try {
            ArrayList list = new ArrayList();
            openConnection();
            ResultSet rs = getResultset("Select TOP " + count + " * From Contacts ORDER BY ContactID DESC");
            while (rs.next()) {
                Contact row = new Contact();
                row.setContactID(rs.getInt("ContactID"));
                row.setName(rs.getString("Name"));
                row.setEmail(rs.getString("Email"));
                row.setMessage(rs.getString("Message"));
                row.setStatus(rs.getInt("Status"));
                row.setCreatedDate(rs.getTimestamp("CreatedDate"));
                list.add(row);
            }
            closeConnection();
            return list.iterator();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public Iterator<Contact> getList(String name, String email, int status, int page) {
        try {
            ArrayList list = new ArrayList();
            openConnection();
            CallableStatement cst = conn.prepareCall("{call spu_pageContacts(?, ?, ?, ?, ?)}");
            cst.setString(1, name);
            cst.setString(2, email);
            cst.setInt(3, status);
            cst.setInt(4, page);
            cst.setInt(5, itemsPerPage);
            ResultSet rs = cst.executeQuery();
            while (rs.next()) {
                Contact row = new Contact();
                row.setContactID(rs.getInt("ContactID"));
                row.setName(rs.getString("Name"));
                row.setEmail(rs.getString("Email"));
                row.setMessage(rs.getString("Message"));
                row.setStatus(rs.getInt("Status"));
                row.setCreatedDate(rs.getTimestamp("CreatedDate"));
                list.add(row);
            }
            closeConnection();
            if (list.size() > 0) {
                return list.iterator();
            }
        } catch (SQLException ex) {
            Logger.getLogger(LocationsDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public int getPageCount(String name, String email, int status) {
        try {
            openConnection();
            CallableStatement cst = conn.prepareCall("{call spu_countPageContacts(?, ?, ?, ?)}");
            cst.setString(1, name);
            cst.setString(2, email);
            cst.setInt(3, status);
            cst.setInt(4, itemsPerPage);
            ResultSet rs = cst.executeQuery();
            int count = 0;
            if (rs.next()) {
                count = rs.getInt("Count");
            }
            closeConnection();

            return count;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return 0;
    }

    public Contact getRow(int id) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Select * From Contacts Where ContactID=?");
            pst.setInt(1, id);
            ResultSet rs = pst.executeQuery();
            Contact row = new Contact();
            if (rs.next()) {

                row.setContactID(rs.getInt("ContactID"));
                row.setName(rs.getString("Name"));
                row.setEmail(rs.getString("Email"));
                row.setMessage(rs.getString("Message"));
                row.setStatus(rs.getInt("Status"));
                row.setCreatedDate(rs.getTimestamp("CreatedDate"));
                closeConnection();
                return row;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public int add(Contact object) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Insert Into Contacts(Name, Email, Message, CreatedDate, Status) " +
                    "Values(?, ?, ?, ?, 0)");
            pst.setString(1, object.getName());
            pst.setString(2, object.getEmail());
            pst.setString(3, object.getMessage());
            pst.setTimestamp(4, new Timestamp(new java.util.Date().getTime()));
            int result = pst.executeUpdate();
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }

    public int updateStatus(int id) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Update Contacts " +
                    "Set Status = 1 Where ContactID = ?");
            pst.setInt(1, id);
            int result = pst.executeUpdate();
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }

    public int delete(int id) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Delete Contacts Where ContactID=?");
            pst.setInt(1, id);
            int result = pst.executeUpdate();
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }
}
